﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using UFC.DBUtility;
using UFC.Common;
using System.Configuration;
using System.Data.OracleClient;
using System.Threading;

namespace MESWorkingReport
{
    public class SendWorkingReportData
    {
        public void RunWorkingReportSync()
        {

            DataTable mydt = GetWorkingReport();
            if (mydt == null || mydt.Rows.Count <= 0)
            {
               // LogHelp.WriteLog("没有可处理的报工记录");
                return;
            }

            Dictionary<string, string> strList;
            //update:Wangjh 20171123 按客户要求取消条件筛选
           // DataRow[] drs = mydt.Select("ordertypename='PD01' or (ordertypename<>'PD01' and islaststep=1 )");
            DataRow[] drs =mydt.Select ();
            //
            foreach (DataRow dr in drs)
            {
                //update:Wangjh 20171123 按客户要求取消条件筛选
                //非批产订单且不是最后一序，不进行报工
                //if (dr["ordertypename"].ToString() != "PD01" && dr["islaststep"].ToString() != "1")
                //{
                //    continue;
                //}

                string strParentSpecNO = dr["parentspecno"].ToString();

                string specname = dr["specname"].ToString();
                string strSpecNo = dr["specno"].ToString();
                if (string.IsNullOrWhiteSpace(strSpecNo))
                {
                    string[] strArray = specname.Split('-');
                    if (strArray.Length >= 3)
                    {
                        strSpecNo = strArray[strArray.Length - 2];
                    }
                }
        
                LogHelp.WriteLog("提交报工 " + dr["mfgordername"].ToString() + " " + dr["containername"].ToString() + " " + strSpecNo);
                strList = new Dictionary<string, string>();
                strList.Add("MfgOrderName", dr["mfgordername"].ToString());//生产订单号
                strList.Add("ContainerName", dr["containername"].ToString());//生产批次号
                if (strSpecNo == "0000")
                    strList.Add("SpecNo", "0001");//工序号
                else
                strList.Add("SpecNo", strSpecNo);//工序号
                strList.Add("ReportDate", dr["txndate"].ToString());//报工时间	
                strList.Add("AcceptanceQty", String.IsNullOrEmpty(dr["qualifiedqty"].ToString()) ? "1" : dr["qualifiedqty"].ToString());//合格数		
                strList.Add("LossQty", dr["scrapqty"].ToString());//报废数
                strList.Add("parentspecno", strParentSpecNO);//父批
                strList.Add("ContainerStep", dr["containerstep"].ToString());
                //strList.Add("IsSubWorkflow", dr["IsSubWorkflow"].ToString());//是否是子工艺
                //strList.Add("FaSpecNo", dr["faspecno"].ToString());//父工序号

                if (InsertWorkingReportRecord(strList))
                {
                    OracleDAL.InsertSyncRecord(dr["containerstep"].ToString(), m_workRecordType, "报工记录创建成功", 0);
                }
                else
                    OracleDAL.InsertSyncRecord(dr["containerstep"].ToString(), m_workRecordType, "报工记录创建失败", 1);
            }
        }

        /// <summary>
        /// 批次获取 Update by Yangsj 20180608
        /// </summary>
        /// <returns></returns>
        private DataTable GetWorkingReport()
        {
            try
            {
                string strSql = @"select * from 
(
 SELECT M.MFGORDERNAME, OT.ORDERTYPENAME, WS.ISLASTSTEP, C.CONTAINERNAME,c.qty, SB.SPECNAME, S.SPECNO, S.PARENTSPECNO, 
 NVL(SUM(CSR2.PASSQTY)+sum(nvl(csr2.defectqty, 0))+sum(nvl(csr2.reworkqty, 0)), 0) QUALIFIEDQTY, 
 NVL(SUM(CSR2.LOSSQTY), 0) SCRAPQTY,
 WR.* 
 FROM (
 SELECT CSR.CONTAINERID, CSR.WORKFLOWSTEPID, TO_DATE(TO_CHAR(SYSDATE, 'yyyy/MM/dd'), 'yyyy/MM/dd') TXNDATE,CSR.CONTAINERID || CSR.WORKFLOWSTEPID CONTAINERSTEP 
 FROM CONTAINERSTEPRECORD CSR 
 WHERE CSR.USECOMPLETED = 1 AND CSR.checkdate >= SYSDATE - 1  --and csr.containername='S10B.15.1007精品/DB06TY'  
 GROUP BY CSR.CONTAINERID, CSR.WORKFLOWSTEPID 
 UNION ALL
  SELECT CSR.CONTAINERID, CSR.WORKFLOWSTEPID, TO_DATE(TO_CHAR(SYSDATE, 'yyyy/MM/dd'), 'yyyy/MM/dd') TXNDATE,CSR.CONTAINERID || CSR.WORKFLOWSTEPID CONTAINERSTEP 
 FROM CONTAINERSTEPRECORD CSR 
 WHERE CSR.USECOMPLETED = 1 AND  csr.createdate>=SYSDATE-1 AND csr.checkdate<SYSDATE-1 --and csr.containername='S10B.15.1007精品/DB06TY'  
 GROUP BY CSR.CONTAINERID, CSR.WORKFLOWSTEPID 
 ) WR 
 LEFT JOIN CONTAINERCURRENTINFO C ON C.CONTAINERID = WR.CONTAINERID INNER JOIN MFGORDER M ON M.MFGORDERID = C.MFGORDERID 
 LEFT JOIN ORDERTYPE OT ON OT.ORDERTYPEID = M.ORDERTYPEID LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = WR.WORKFLOWSTEPID 
 LEFT JOIN SPEC S ON S.SPECID = WS.SPECID LEFT JOIN SPECBASE SB ON SB.SPECBASEID = S.SPECBASEID 
  LEFT JOIN FACTORY FA ON FA.FACTORYID = C.ORIGINALFACTORYID 
  LEFT JOIN CONTAINERSTEPRECORD CSR2 ON CSR2.CONTAINERID=WR.CONTAINERID AND CSR2.WORKFLOWSTEPID=WR.WORKFLOWSTEPID
  WHERE not exists (select 1 from WRITETOERPRECORD WTE where WTE.RECORDID = WR.CONTAINERSTEP and WTE.RECORDTYPE = 'workingreport' AND WTE.ISUSE = 0)
  AND FA.FACTORYNAME NOT LIKE '装配试车%' and FA.FACTORYNAME not like '%热表%' AND M.CDOTYPEID = 1620 --AND ROWNUM<1000 
  group by M.MFGORDERNAME, OT.ORDERTYPENAME, WS.ISLASTSTEP, C.CONTAINERNAME,c.qty, SB.SPECNAME, S.SPECNO, S.PARENTSPECNO,wr.CONTAINERID,wr.WORKFLOWSTEPID,wr.TXNDATE,wr.CONTAINERSTEP
) t where --t.QUALIFIEDQTY>=qty and 
rownum<1001 ";//每次处理999条记录 and FA.FACTORYNAME not like '%叶片%' 
                DataSet myDS = OracleHelper.Query(strSql.ToString());
                if (myDS == null || myDS.Tables.Count == 0)
                {
                    return null;
                }
                return myDS.Tables[0];
            }
            catch (Exception e)
            {
                LogHelp.WriteError("批次获取", e);
                return null;
            }
        }

        private bool InsertWorkingReportRecord(Dictionary<string, string> strList)
        {
            //string updateSql = string.Format("UPDATE MES_WORKINGREPORT_TABLE SET ISUSE=1 ,STATUS=0,reportdate=to_date(to_char(SYSDATE,'yyyy/MM/dd'),'yyyy/MM/dd') WHERE isuse=0 and containerstep = '{0}'", strList["ContainerStep"]);
            //OracleHelper.GetSingle(updateSql);

            string strSql = "insert into mes_workingreport_table " +
           "(mfgordername, containername, specno, sonspecno, reportdate, REPORTQTY, lossqty, lossreason, status,id,CONTAINERSTEP,createdate)" +
           "values (:v_mfgordername, :v_containername, :v_specno, :sonspecno, :v_reportdate, :REPORTQTY, :v_lossqty, '', 0,:id,:CONTAINERSTEP,sysdate)";

            OracleParameter[] insertParam = new OracleParameter[9];

            insertParam[0] = new OracleParameter(":v_mfgordername", OracleType.VarChar);
            insertParam[0].Direction = ParameterDirection.Input;
            insertParam[0].Value  =strList["MfgOrderName"];

            insertParam[1] = new OracleParameter(":v_containername", OracleType.VarChar);
            insertParam[1].Direction = ParameterDirection.Input;
            insertParam[1].Value = strList["ContainerName"];

            insertParam[2] = new OracleParameter(":v_specno",OracleType.VarChar);
            insertParam[2].Direction = ParameterDirection.Input;

            if (strList["parentspecno"].Trim() == "")
            {
                insertParam[2].Value = strList["SpecNo"];
            }
            else
            {
                insertParam[2].Value = strList["parentspecno"];
            }


            insertParam[3] = new OracleParameter(":sonspecno",OracleType.VarChar);
            insertParam[3].Direction = ParameterDirection.Input;

            if (strList["parentspecno"].Trim() != "")
            {
                insertParam[3].Value = strList["SpecNo"];
            }
            else
            {
                insertParam[3].Value = strList["parentspecno"];
            }
         
            insertParam[4] = new OracleParameter(":v_reportdate",OracleType.DateTime);
            insertParam[4].Direction = ParameterDirection.Input;
            insertParam[4].Value = DateTime.Parse(strList["ReportDate"]);

            insertParam[5] = new OracleParameter( ":REPORTQTY",OracleType.Number);
            insertParam[5].Direction = ParameterDirection.Input;
            insertParam[5].Value = Convert.ToInt32(strList["AcceptanceQty"]);

            insertParam[6] = new OracleParameter(":v_lossqty",OracleType.Number);
            insertParam[6].Direction = ParameterDirection.Input;
            insertParam[6].Value = Convert.ToInt32(strList["LossQty"]);

            insertParam[7] = new OracleParameter( ":ID", OracleType.VarChar);
            insertParam[7].Direction = ParameterDirection.Input;
            insertParam[7].Value = Guid .NewGuid().ToString ();

            insertParam[8] = new OracleParameter(":CONTAINERSTEP", OracleType.VarChar);
            insertParam[8].Direction = ParameterDirection.Input;
            insertParam[8].Value = strList["ContainerStep"];

            int i = OracleHelper.ExecuteSql(strSql, insertParam);

            return (i > 0) ? true : false;
        }

                /// <summary>
        /// 装饰大阶段报功集成数据处理 create by tianFW　2018年11月9日
        /// </summary>
        public void RunZSWorkingReportSync()
        {
            DataTable mydt = GetZSWorkingReport();
            if (mydt == null || mydt.Rows.Count <= 0)
            {
                return;
            }
            Dictionary<string, string> strList;
            DataRow[] drs = mydt.Select();
            foreach (DataRow dr in drs)
            {
                string strParentSpecNO = dr["parentspecno"].ToString();
                string specname = dr["specname"].ToString();
                string strSpecNo = dr["specno"].ToString();
                if (string.IsNullOrWhiteSpace(strSpecNo))
                {
                    string[] strArray = specname.Split('-');
                    if (strArray.Length >= 3)
                    {
                        strSpecNo = strArray[strArray.Length - 2];
                    }
                }

                LogHelp.WriteLog("提交ZS大阶段报工 订单：" + dr["mfgordername"].ToString() + " 大阶段批次" + dr["containername"].ToString() + " 工序号" + strSpecNo);
                strList = new Dictionary<string, string>();
                strList.Add("MfgOrderName", dr["mfgordername"].ToString());//生产订单号
                strList.Add("ContainerName", dr["containername"].ToString());//生产批次号
                if (strSpecNo == "0000")
                    strList.Add("SpecNo", "0001");//工序号
                else
                    strList.Add("SpecNo", strSpecNo);//工序号
                strList.Add("ReportDate",dr["txndate"].ToString());//报工时间	
                strList.Add("AcceptanceQty", "1");//合格数	装试默认为1	
                strList.Add("LossQty", "0");//报废数 装试默认为0	
                strList.Add("parentspecno", strParentSpecNO);//父批
                strList.Add("ContainerStep", dr["containerstep"].ToString());
                if (InsertWorkingReportRecord(strList))
                {
                    OracleDAL.InsertSyncRecord(dr["containerstep"].ToString(), m_workRecordType, "ZS大阶段报工记录创建成功", 0);
                }
                else
                    OracleDAL.InsertSyncRecord(dr["containerstep"].ToString(), m_workRecordType, "ZS大阶段报工记录创建失败", 1);
            }
        }

        /// <summary>
        /// 获取装饰报功集成数据 create by tianFW 2018年11月9日
        /// </summary>
        /// <returns></returns>
        private DataTable GetZSWorkingReport()
        {
            try
            {
                //Modify by Yuxj20190419 由获取默认件号更改为取已经分解时的件号
                //增加语句LEFT JOIN product p ON p.productid = get_split_mfgorder_productid(m.mfgorderid)
                string strSql = @"SELECT M.MFGORDERNAME, EP.CONTAINERNAME, SB.SPECNAME,  S.SPECNO, S.PARENTSPECNO,
                               TO_DATE(TO_CHAR(ep.finishdate, 'yyyy/MM/dd'), 'yyyy/MM/dd') TXNDATE, EP.ENGINEPARTPLANID CONTAINERSTEP
                          FROM ENGINEPARTPLAN EP
                          LEFT JOIN MFGORDER M  ON M.MFGORDERID = EP.MFGORDERID
                          LEFT JOIN WORKFLOWSTEP WS  ON WS.WORKFLOWSTEPID = EP.WORKFLOWSTEPID
                          LEFT JOIN SPECBASE SBB  ON SBB.SPECBASEID = WS.SPECBASEID
                          LEFT JOIN SPEC S  ON S.SPECID = NVL(SBB.REVOFRCDID, WS.SPECID)
                          LEFT JOIN SPECBASE SB ON SB.SPECBASEID = S.SPECBASEID
                          LEFT JOIN PRODUCT P  ON P.PRODUCTID = GET_SPLIT_MFGORDER_PRODUCTID(M.MFGORDERID)
                          LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = P.PRODUCTBASEID
                          LEFT JOIN WRITETOERPRECORD WTE ON WTE.RECORDTYPE = 'workingreport' AND WTE.RECORDID = EP.ENGINEPARTPLANID AND WTE.ISUSE = 0
                         WHERE EP.FINISHDATE>SYSDATE-100 AND NVL(WTE.RECORDID,'0')='0' AND EP.USECOMPLETED = 1
                        AND EP.PRODUCTID = P.PRODUCTID AND EP.WORKFLOWSTEPID >'0' AND  EP.CONTAINERNAME IS NULL   AND ROWNUM<1000";//每次处理999条记录
                DataSet myDS = OracleHelper.Query(strSql.ToString());
                if (myDS == null || myDS.Tables.Count == 0)
                {
                    return null;
                }
                return myDS.Tables[0];
            }
            catch (Exception e)
            {
                LogHelp.WriteError("ZS大阶段报功信息获取", e);
                return null;
            }
        }

        public void RunWorkingReportSyncJX()
        {
            DataTable mydt = GetWorkingReport();
            if (mydt == null || mydt.Rows.Count <= 0)
            {
                LogHelp.WriteLog("没有可处理的报工记录");
                return;
            }

            Dictionary<string, string> strList;
            // DataRow[] drs = mydt.Select("ordertypename='PD01' or (ordertypename<>'PD01' and islaststep=1 )");//update:Wangjh 20180126 按客户要求取消条件筛选

            foreach (DataRow dr in mydt.Rows )
            {
                //非批产订单且不是最后一序，不进行报工 //update:Wangjh 20180126 按客户要求取消条件筛选
                //if (dr["ordertypename"].ToString() != "PD01" && dr["islaststep"].ToString() != "1")
                //{
                //    continue;
                //}
                string strParentSpecNO = dr["parentspecno"].ToString();

                string specname = dr["specname"].ToString();
                string strSpecNo = dr["specno"].ToString();
                if (string.IsNullOrWhiteSpace(strSpecNo))
                {
                    string[] strArray = specname.Split('-');
                    if (strArray.Length >= 3)
                    {
                        strSpecNo = strArray[strArray.Length - 2];
                    }
                }

                LogHelp.WriteLog("提交报工 " + dr["mfgordername"].ToString() + " " + dr["containername"].ToString() + " " + strSpecNo);
                strList = new Dictionary<string, string>();
                strList.Add("MfgOrderName", dr["mfgordername"].ToString());//生产订单号
                strList.Add("ContainerName", dr["containername"].ToString());//生产批次号
                if (strSpecNo == "0000")
                    strList.Add("SpecNo", "0001");//工序号
                else
                    strList.Add("SpecNo", strSpecNo);//工序号
                strList.Add("ReportDate", dr["txndate"].ToString());//报工时间	
                strList.Add("AcceptanceQty", String.IsNullOrEmpty(dr["qualifiedqty"].ToString()) ? "1" : dr["qualifiedqty"].ToString());//合格数		
                strList.Add("LossQty", dr["scrapqty"].ToString());//报废数
                strList.Add("parentspecno", strParentSpecNO);//父批
                if (InsertWorkingReportRecord(strList))
                {
                    OracleDAL.InsertSyncRecord(dr["speclastcheckhistoryid"].ToString(), m_workRecordType, "报工记录创建成功", 0,true );
                }
                else
                    OracleDAL.InsertSyncRecord(dr["speclastcheckhistoryid"].ToString(), m_workRecordType, "报工记录创建失败", 1,true );
            }
        }

        private const string m_workRecordType = "workingreport";
    }
}
